#!/use/bin/python
# coding=utf-8
# 早间开机 数据分析-早间开机-播放时间分布
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数

    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur


# 记录 数据分析-早间开机-播放时间分布 analysis_zjkj_duration
def insertZjkjDuration(date):
    try:
        conn, cur = getDataConnection()
        #进行 数据操作 打印日志
        logDetail =f"{date}日-执行播放时间分布"
        print(logDetail)
        saveLog(logDetail, date)
        #先删除音乐播放时长相关
        deleteMusicSql = f"delete from analysis_zjkj_duration  where scene ='music' and ref_date = '{date}'"
        cur.execute(deleteMusicSql)
        # 再插入  30S为一个间隔时间
        insertMusicSql = f"""
                insert into analysis_zjkj_duration(duration_unit,duration_num,scene,ref_date)
                select tt.duration,count(1) num,'music','{date}'  from (
                select TRUNCATE((JSON_EXTRACT(t.msg,'$.duration'))/1000/30,0)+1 duration,t.active_id from log_common_service t where t.`key` = 'musicPageFin'
                and DATE_FORMAT(t.create_time, '%Y-%m-%d') ='{date}') tt group by tt.duration
        """
        cur.execute(insertMusicSql)
        # 先删除新闻播放时长相关
        deleteNewsSql = f"delete from analysis_zjkj_duration  where scene ='news' and ref_date = '{date}'"
        cur.execute(deleteNewsSql)
        # 再插入  5Min为一个间隔时间
        insertNewsSql = f"""
                        insert into analysis_zjkj_duration(duration_unit,duration_num,scene,ref_date)
                        select tt.duration,count(1) num,'news','{date}'  from (
                        select TRUNCATE((JSON_EXTRACT(t.msg,'$.duration'))/1000/60/5,0)+1 duration,t.active_id from log_common_service t where t.`key` = 'newsPageFin'
                        and DATE_FORMAT(t.create_time, '%Y-%m-%d') ='{date}') tt group by tt.duration having duration<=6
                """
        cur.execute(insertNewsSql)
        # 先删除早间播报总体播放时长相关
        deleteTotalSql = f"delete from analysis_zjkj_duration  where scene ='total' and ref_date = '{date}'"
        cur.execute(deleteTotalSql)
        # 再插入  5Min为一个间隔时间
        insertTotalSql = f"""
                                insert into analysis_zjkj_duration(duration_unit,duration_num,scene,ref_date)
                                select tt.duration,count(1) num,'total','{date}'  from (
                                select TRUNCATE((JSON_EXTRACT(t.msg,'$.totalTime'))/1000/60/5,0)+1 duration,t.active_id from log_common_service t where t.`key` = 'tipTaskFinish'
                                and DATE_FORMAT(t.create_time, '%Y-%m-%d') ='{date}') tt group by tt.duration having duration<=7
                        """
        cur.execute(insertTotalSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()




# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date


def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 50
        jobName = '	核心指标-早间开机-播放时间分布'
        tableName = 'analysis_zjkj_duration'
        # 插入日志表
        saveLogSql = f"""insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                                                value({jobId},'{jobName}','{detail}','{tableName}','{ref_date}')"""
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    d = 1
    date = getdate(d)
    print (f"{date} 日期,更新<数据分析-早间开机-播放时间分布>报表")
    insertZjkjDuration(date)
